package com.zy.dao.impl;

import com.zy.dao.EmployeeDao;
import com.zy.pojo.Employee;
import com.zy.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: xiaoqi
 * Date: 2021/5/31
 * Time: 下午 16:28
 * Description: 员工持久层层接口实现类
 */
public class EmployeeDaoImpl implements EmployeeDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public Integer insertEmployee(Employee employee) {
        String sql = "INSERT INTO `employee`(`name`, `password`, `phone`, `email`, `enterDate`, `departmentId`, `img`) VALUES ( ?, ?, ?, ?, ?, ?, ?)";
        Integer count = jdbcTemplate.update(sql, employee.getName(), employee.getPassword(), employee.getPhone(), employee.getEmail(), employee.getEnterDate(), employee.getDepartmentId(), employee.getImg());
        String select = "select max(id) from employee";
        Integer id = jdbcTemplate.queryForObject(select, Integer.class);
        employee.getRole().setEmpId(id);
        return count;
    }

    @Override
    public Integer deleteEmployeeById(Integer id) {
        String sql = "delete from employee where id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Integer updateEmployee(Employee employee) {
        String sql = "UPDATE `employee` SET `name` = ?, `password` = ?, `phone` = ?, `email` = ?, `enterDate` = ?, `departmentId` = ?, `img` = ? WHERE `id` = ?";
        return jdbcTemplate.update(sql, employee.getName(), employee.getPassword(), employee.getPhone(), employee.getEmail(), employee.getEnterDate(), employee.getDepartmentId(), employee.getImg(), employee.getId());
    }

    @Override
    public List<Employee> getEmployeeList(Integer start, Integer end, Employee employee) {
        String sql = "select * from employee where 1 = 1 ";
        ArrayList<Object> params = new ArrayList<>();
        if (!"".equals(employee.getName()) && null != employee.getName()) {
            sql += " and name like concat('%' , ? , '%') ";
            params.add(employee.getName());
        }

        if (!"".equals(employee.getPhone()) && null != employee.getPhone()) {
            sql += " and phone like concat('%' , ? , '%') ";
            params.add(employee.getPhone());
        }

        if (!"".equals(employee.getEmail()) && null != employee.getEmail()) {
            sql += " and email like concat('%' , ? , '%') ";
            params.add(employee.getEmail());
        }

        if (employee.getDepartmentId() != null) {
            sql += " and departmentId = ? ";
            params.add(employee.getDepartmentId());
        }

        sql += " order by enterDate asc limit ? , ? ";
        params.add(start);
        params.add(end);
        System.out.println(sql);
        System.out.println(params);
        List<Employee> list = new ArrayList<>();
        try {
            list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class), params.toArray());
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return list;
        }
    }

    @Override
    public Employee getEmployeeById(Integer id) {
        String sql = "select * from employee where id = ?";
        Employee employee = null;
        try {
            employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Employee>(Employee.class), id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return employee;
        }
    }

    @Override
    public Integer getEmployeeCountByName(String name) {
        String sql = "select count(*) from employee where name = ?";
        Integer count = 0;
        try {
            count = jdbcTemplate.queryForObject(sql, Integer.class, name);
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return count;
        }
    }

    @Override
    public Employee getEmployeeByNameAndPassword(String name, String password) {
        String sql = "select * from employee where name = ? and password = ?";
        Employee employee = null;
        try {
            employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Employee>(Employee.class), name, password);
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return employee;
        }
    }

    @Override
    public Integer getEmployeeCount(Employee employee) {
        String sql = "select count(*) from employee where 1 = 1 ";
        ArrayList<Object> params = new ArrayList<>();
        if (!"".equals(employee.getName()) && null != employee.getName()) {
            sql += " and name like concat('%' , ? , '%') ";
            params.add(employee.getName());
        }

        if (!"".equals(employee.getPhone()) && null != employee.getPhone()) {
            sql += " and phone like concat('%' , ? , '%') ";
            params.add(employee.getPhone());
        }

        if (!"".equals(employee.getEmail()) && null != employee.getEmail()) {
            sql += " and email like concat('%' , ? , '%') ";
            params.add(employee.getEmail());
        }

        if (employee.getDepartmentId() != null) {
            sql += " and departmentId = ? ";
            params.add(employee.getDepartmentId());
        }
        System.out.println(sql);
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, params.toArray());
        return count;
    }
}